ALTER           proc sp_update_packing_slip_status 
  @customer_shipping_id int,
  @status varchar(20), 
  @no_of_boxes int,
  @tracking varchar(50),
  @shippedVia varchar(100),
  @psweight float,
  @contactPerson varchar(50),
  @invoiceNo varchar(50),
  @shipDate varchar(50),
  @shippingInstructions varchar(300),
  @comments varchar(300),
  @freightInstructions varchar(300),
  @gross_weight float,
  @uom_gross_weight varchar(10),    
  @pkg_dimension varchar(20), 
  @uom_pkg_dimension varchar(10),    
  @mawb varchar(50),
  @hawb varchar(50),
  @flight_no_one varchar(50),
  @flight_no_two varchar(50),
  @etd varchar(50),
  @eta varchar(50)
  as 
  begin
  	declare @cslit_cursor CURSOR
  	declare @lot_cursor CURSOR
  	declare @cslit_id int
  	declare @product_type varchar(50)
  	declare @lot_id int
  	declare @qty_pull int
 	declare @drop_shipment int
 	declare @lot_date_in datetime
	declare @lot_entered_by bit
  	declare @il_qty_balance_existing int

	IF ( @status = 'SHIPPED' )
	 BEGIN
		DECLARE @DB_INV_NO VARCHAR(50)
		SELECT @DB_INV_NO = Inv_No FROM CUSTOMER_SHIPPING_TABLE WHERE CUSTOMERSHIPPING_ID = @CUSTOMER_SHIPPING_ID
		--AVOIDING UPDATING NEW INV NO IF INV NO ALREADY EXISTS IN DB
		--THIS IS DOING SINCE NEW INV NO IS UPDATING WHEN CLICKED CONFIRM BUTTON DOUBLE TIME CAUSING INV NO SEQUENCE MISSING
		IF ( @DB_INV_NO IS NOT NULL AND LEN(LTRIM(RTRIM(@DB_INV_NO))) > 0 )
		 BEGIN
			SET @invoiceNo = @DB_INV_NO
		 END
		UPDATE CUSTOMER_SHIPPING_TABLE SET CST_CONFIRMED_ON = CURRENT_TIMESTAMP WHERE CUSTOMERSHIPPING_ID = @CUSTOMER_SHIPPING_ID
	 END

  	update customer_shipping_table set cst_status = @status,CST_Box_Count = @no_of_boxes,CST_AW_Bill_Number = @tracking, CST_Weight = @psweight,CST_Method = @shippedVia,CST_Shipping_Contact_Person = @contactPerson,Inv_No=@invoiceNo,CST_Ship_Date=@shipDate,CST_Shipping_Instructions=@shippingInstructions,CST_comments=@comments, CST_Freight_Instructions=@freightInstructions, CST_Gross_Weight = @gross_weight,
	CST_Package_Dimension = @pkg_dimension, CST_UOM_Weight = @uom_gross_weight, CST_UOM_Pkg_Dimension = @uom_pkg_dimension, CST_MAWB = @mawb, CST_HAWB = @hawb, CST_FLIGHT_NUMBER = @flight_no_one, CST_FLIGHT_NUMBER_TWO = @flight_no_two, CST_ETD = @etd, CST_ETA = @eta
	where customershipping_id = @customer_shipping_id
	
  	update customer_shipping_line_item_table set cslit_Status = @status where customershipping_id = @customer_shipping_id
  	
  	set @cslit_cursor = CURSOR FOR
  	select cslit_id, isnull (p_type, 'CHIP') as P_type from customer_shipping_line_item_table cslit, customer_open_order_lineItem cool, product where cool.product_ID = product.product_ID and cslit.LineItem_ID = cool.LineItem_ID and  customershipping_id = @customer_shipping_id
  	-- select cslit_id from customer_shipping_line_item_table where customershipping_id = @customer_shipping_id
  	
  	open @cslit_cursor
  
  		fetch next from @cslit_cursor into @cslit_id, @product_type
  
  		while @@fetch_status = 0
  		begin
  			update customer_shipping_lot_number_table set cslnt_status = @status where cslit_id = @cslit_id
 			select @drop_shipment = CST_IS_Drop_Shipment from customer_shipping_table where customershipping_id = @customer_shipping_id
			
			/*
			set @lot_cursor = CURSOR FOR select Lot_ID, CSLNT_Qty_Pull from customer_shipping_lot_number_table where cslit_id = @cslit_id
			open @lot_cursor
			fetch next from @lot_cursor into @lot_id, @qty_pull
			
			while @@fetch_status = 0
			begin
				select @lot_date_in = IL_Date_In from Inventory_Lot where Lot_Id = @lot_id
				Select @lot_entered_by = ISNULL(Lot_Entered_By,1) FROM Lot_Table Where Lot_ID = @lot_id
				--
				--	Updating the Inventory Bal when the Lot is entered by the Data Entry	
				--
				
				
				if( @product_type = 'CHIP' AND @lot_entered_by = 0 ) 
	  			begin
					if @drop_shipment = 0 
					begin
						insert into inventory_lot_history ( Lot_Id, IL_Date_In, IL_Date_Out, TimeStamp ) values ( @lot_id, @lot_date_in, @shipDate, CURRENT_TIMESTAMP )

						select @il_qty_balance_existing = IL_Qty_Balance from Inventory_lot where Lot_Id = @lot_id
						if @il_qty_balance_existing is null 
						begin
							update inventory_lot set IL_Qty_Balance = IL_Qty_In - @qty_pull where Lot_ID = @lot_id
						end
						else
						begin
							update inventory_lot set IL_Qty_Balance = IL_Qty_Balance - @qty_pull where Lot_ID = @lot_id
						end
					end
				end

				if( @product_type <> 'CHIP' )
  				begin
					insert into inventory_lot_history ( Lot_Id, IL_Date_In, IL_Date_Out, TimeStamp ) values ( @lot_id, @lot_date_in, @shipDate, CURRENT_TIMESTAMP )
					if @drop_shipment = 1 
					begin
	 					update inventory_lot set IL_Qty_In = IL_Qty_In - @qty_pull where Lot_ID = @lot_id
					end
					else
					begin
	 					update inventory_lot set IL_Qty_In = IL_Qty_In - @qty_pull, IL_Qty_Balance = IL_Qty_Balance - @qty_pull where Lot_ID = @lot_id
					end
				end	
				set @il_qty_balance_existing = null 
				fetch next from @lot_cursor into @lot_id, @qty_pull		
			end
			close @lot_cursor
			deallocate @lot_cursor */
  			fetch next from @cslit_cursor into @cslit_id, @product_type

			
  		end
  
  	close @cslit_cursor
  	deallocate @cslit_cursor
  end	
